Spreadsheets as a Tool for Teaching Simulation

Simulation can be a difficult subject to teach, as most commercial software requires learning new approaches and time is limited, particularly in business overview courses. Spreadsheets have a distinct advantage in that most students developed a high level of expertise prior to taking management science courses. As such, they provide an ideal platform on which to learn the basics of simulation methodology. In this paper, we describe the use of spreadsheets as a tool for teaching simulation for both pure Monte-Carlo approaches (focusing on the advantages of using Excel add-ins), and for simple systems simulation models such as queueing. ________________________________________ Simulation models are logical descriptions of the interrelationships among elements of a decision problem, or the sequence of events that occur in a system over time. They capture probabilistic behavior and allow one to estimate key statistics such as means or tail probabilities of distributions that cannot be derived analytically. Simulation models can be categorized into two broad categories. Systems simulation models capture the dynamics and behavior of interacting elements of a system, such as a manufacturing facility or call center. These models are driven by changes that occur in the system over time. Monte-Carlo simulation models, on the other hand, are timeindependent. They involve repeated sampling from probability distributions of model inputs to characterize the distributions of model outputs. Monte-Carlo simulation models generate distributions of potential outcomes of key model variables along with their likelihood of occurrence. Simulation has been an important tool in management science throughout its history. Most applications of simulation have revolved around dynamic systems -for instance, inventory control, project planning, queueing and scheduling, production, material handling and logistics, and service and communication systems [Pritsker, et al., 1994]. Although Monte-Carlo simulation has been around for decades, it is only recently that practitioners have demonstrated a renewed interest in the subject, particularly for risk assessment applications [Nichols, 1994; Whitford, 1997]. Risk simulation has become a significant tool in finance and marketing analysis, as well as other areas of application. The principal focus on simulation in most engineering schools has been on dynamic systems, while many business schools emphasize Monte-Carlo simulation. However, much cross-over exists, as Monte-Carlo simulation finds useful applications in engineering systems, and business process reengineering relies on dynamic system models. Implementing system simulation models typically requires a commercial simulation package such as SLAM, Arena, Extend, SIMSCRIPT, or GPSS. While these powerful packages offer many significant advantages to commercial users, they can often be difficult to introduce in survey courses in management science, primarily because of the time involved in learning the terminology and procedures required to run the software Although “point-and-click” object-oriented approaches have alleviated this significantly, commercial packages still require a high amount of startup, particularly in a short academic term for an introductory overview course. Spreadsheets, on the other hand, offer many pedagogical advantages for learning simulation. These include •= Rapid start-up -Because the basics of spreadsheets are taught in introductory courses such as accounting or statistics, students can easily understand model implementation issues in simulation. •= Integrated graphics -Spreadsheets allow users to quickly and easily develop visualizations of data, and gain useful insights that typical simulation output does not provide. EVANS Spreadsheets as a Tool for Teaching Simulation _____________________________________________________________________________________________ _____________________________________________________________________________________________ INFORMS Transactions on Education 1:1 (27-37) INFORMS 28 •= Dynamic updating -New results can be immediately obtained as data are changed. Of particular interest from a teaching perspective is the dynamic updating of graphs and charts as new results are obtained. •= Statistics integration -A full complement of statistical tools and functions allow users to perform on-the-spot analyses of results without having to transfer data to other software packages. Monte-Carlo simulation is particularly well-suited to spreadsheet models. In fact, we conjecture that the recent popularity of Monte-Carlo risk analysis methods is due to technology advances: powerful desktop computers and user-friendly software designed around spreadsheets, such as Crystal Ball and @Risk. The purpose of this paper is to discuss some key issues associated with teaching simulation methods using Excel spreadsheets and to provide some illustrative examples. We will discuss both Monte-Carlo simulation as well as dynamic systems simulation, focusing on implementation, advantages, and limitations. Building Spreadsheet Simulation Models In general, building a simulation model involves the following steps: 1. Formulating the problem to determine the objectives of the simulation study, the system performance measures to be computed, and the scenarios to be evaluated. A scenario is any specification of the controllable inputs, that is, a particular variation of the system that we wish to study. By comparing the performance of different scenarios, we expect to be able to draw conclusions as to which scenario is the best, if indeed there is a significant difference. 2. Developing a logical model of the system under study that describes key events and actions that take place. Visual models provide the basis for implementing the simulation model on a spreadsheet. For systems simulations, the model is usually described with a flowchart; for MonteCarlo simulations, one often uses an influence diagram. 3. Specifying probabilistic assumptions needed to drive the simulation, that is, the probability distributions of input variables. If historical data are available, we can use the empirical distribution to drive the simulation, or fit the data to a theoretical distribution using data fitting techniques. If not, then a distribution must be selected judgmentally. 4. Implementing the model on a computer to perform the necessary calculations required to evaluate the distribution of model outputs. One of the advantages of using spreadsheets is that an experienced spreadsheet user can develop a model rapidly by exploiting the built-in functions and structure of the spreadsheet. We first illustrate an example of a Monte-Carlo simulation model using the capabilities of Excel as the simulation driver. Figure 1 shows a simple newsboy simulation with a demand distribution that is normal with a mean of 60 and standard deviation of 10. The basic data are given in the range A1:B9. Random normally-distributed demands in cell B8 can be generated using the Excel formula =NORMINV(RAND(),60,10) (although we assumed a continuous distribution, the random variate can easily be made discrete). Of course, any mechanism for generating random variates for the proper distribution can be used. This is, however, one of the limitations of Excel. Although Excel provides a random number generation routine through the Analysis Toolpak; the scope of available distributions is very limited. In many cases, the user must explicitly write a formula for generating random variates using the RAND() function, similar to the way one needs to implement simulations using general purpose languages such as FORTRAN or BASIC. Such generating formulas can be found in many simulation texts; for example, Law and Kelton (1991). EVANS Spreadsheets as a Tool for Teaching Simulation _____________________________________________________________________________________________ _____________________________________________________________________________________________ INFORMS Transactions on Education 1:1 (27-37) INFORMS 29 Figure 1: Spreadsheet model and data table simulation of a newsboy problem The actual simulation is implemented using the Excel Data Table feature in cells D1:J12. Each row corresponds to a single trial, and each column evaluates an alternative order quantity. In this sense, Excel provides a significant advantage over general-purpose programming or simulation languages that would require the evaluation of each alternative independently within a programming loop. Several authors, such as Plane (1994), Ragsdale (1998) and Winston and Albright (1997) have described how data tables can be used to replicate samples in a Monte-Carlo fashion by selecting a blank cell for the column input cell in the Excel dialog box. (The row input cell would be the order quantity, B7.) However, another Excel limitation is that it does not allow one to set a random number seed to control the stream of random numbers. Thus, each column represents an independent sample, and statistical techniques for variance reduction cannot be used. Summary statistical information in the range D14:J16 is derived from standard Excel functions. These statistics can be graphed easily using the Excel charting procedure as shown in Figure 1. The integrated graphical features of spreadsheets 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 A B C D E F G H I J Newsboy Simulation Trial 270.00 $ 40 50 60 70 80 90 Selling price 12.00 $ 1 180 $ 225 $ 270 $ 259 $ 255 $ 226 $ Cost 7.50 $ 2 180 $ 225 $ 270 $ 313 $ 291 $ 181 $ Discount price 6.00 $ 3 180 $ 225 $ 210 $ 223 $ 131 $ 231 $ 4 180 $ 215 $ 236 $ 315 $ 273 $ 241 $ Order Quantity 60 5 180 $ 225 $ 221 $ 259 $ 209 $ 110 $ Demand 60.80222 6 180 $ 225 $ 196 $ 187 $ 126 $ 123 $ Profit 270.00 $ 7 180 $ 225 $ 270 $ 269 $ 281 $ 243 $ 8 180 $ 152 $ 230 $ 257 $ 233 $ 237 $ 9 180 $ 136 $ 256 $ 238 $ 278 $ 237 $ 10 180 $ 225 $ 270 $ 195 $ 295 $ 238 $ Average 180 $ 208 $ 243 $ 251 $ 237 $ 207 $ Std. Dev. 0 33.832 28.09 43.05 63.29 50.9 CV 0.00 0.16 0.12 0.17 0.27 0.25 Order Quantity $$50 $100 $150 $200 $250 $300 $350 1 2 3 4 5 6 0.00 0.05 0.10 0.15 0.20 0.25 0.30 Average profit Coeff. Of Variation EVANS Spreadsheets as a Tool for Teaching Simulation _____________________________________________________________________________________________ _____________________________________________________________________________________________ INFORMS Transactions on Education 1:1 (27-37) INFORMS 30 provide simple interpretations of the output data; in this example, we see that as the order quantity increases, so generally does the coefficient of variation, indicating a relatively higher level of risk to reward. From a pedagogical perspective, graphics also provide the ability to illustrate variation associated with simulation output easily. For example, by pressing the F9 key, the entire simulation is replicated, allowing one to show changes in the output. For this example, repeated simulations will show different optimal order quantities and relative risk levels. In this fashion, students may begin to obtain a sense of risk associated with the recommendations from a simulation. These concepts can be easily tied into prior statistical concepts such as sampling from populations and the standard error of the mean. We have noted some of the limitations of bruteforce Excel simulations like this example. The major limitations are the lack of automation of the replication process and easy ability to analyze results. Fortunately, these are alleviated by addins such as Crystal Ball, which we discuss next. Add-ins also offer extensive random variate functions unavailable in Excel and the ability to develop customized applications through Visual Basic. Crystal Ball for Monte-Carlo Risk Analysis Crystal Ball is a simulation add-in to Microsoft Excel, and provides a far higher degree of flexibility and user-friendliness than using Excel alone. Crystal Ball, a product of Decisioneering, Inc., automates some of the more complex tasks required in Monte-Carlo simulations, such as generating random variates, replicating the spreadsheet, aggregating results, and computing statistics. Crystal Ball requires the specification of two sets of cells in a spreadsheet model: 1. Assumption Cells cells that represent uncertain inputs in the simulation model, and which are defined by some probability distribution 2. Forecast Cells cells that represent the outputs of the simulation model Selecting Define Assumption from the Crystal Ball menu will invoke the Crystal Ball Distribution Gallery (Figure 2) which will allow you to select the appropriate probability distribution and parameter settings. A variety of known distributions are available as well as the capability of defining a distribution from empirical data. The Distribution Gallery eliminates the need to write explicit functions for random variate generation. Crystal Ball also includes a data fitting capability that estimates best fits and parameter values for driving the simulation. Figure 2: Crystal Ball Distribution Gallery screen EVANS Spreadsheets as a Tool for Teaching Simulation _____________________________________________________________________________________________ _____________________________________________________________________________________________ INFORMS Transactions on Education 1:1 (27-37) INFORMS 31 Implementing the newsboy simulation in Crystal Ball requires only specification of the basic model parameters in cells A1:B9 in Figure 1. Cell B8 is defined as an assumption cell with a normal distribution chosen from the Distribution Gallery. A data table can be used to evaluate a range of decision variables, with each entry in the data table being defined as a Crystal Ball forecast cell. Crystal Ball generates a forecast window for each forecast cell, an example of which is shown in Figure 3 for 1000 trials. The spike in the distribution corresponds to the profit associated for demands exceeding the order quantity of 80. The forecast window can be manipulated graphically to evaluate probabilities for specified ranges or to evaluate ranges for certainty levels. In addition Crystal Ball provides basic statistical information as shown in Figure 4. Figure 3: Crystal Ball newsboy simulation 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24